#!/usr/bin/env python

"""
PostgreSQL data types.
"""
__revision__ = "$Id$"
__docformat__ = 'reStructuredText'

import mx.DateTime , mx.DateTime.ISO
from sql import sql_quote , sql_quote_from
#-----------------------------------------------------------

class SqlValueError(ValueError) : pass
#-----------------------------------------------------------

class sql_base :
    """
    Abstract base type.

    Methods:
    cls.isValid(v) - check that value is valid for initializer
    cls.check(v) - raises exception if not valid
    cls.max - maximum value (for integers)
    cls.min - minimal value (for integers)
    self.sql() - quute for sql request
    self.sql_from(delimiter) - quote for sql copy from
    self.values - tuple of all possible values
    self.descriptions - dictionary - values descriptions
    cls.precision,
    cls.scale - numeric field parameters
    cls.max_length - maximum text length
    cls.description - class description
    cls.sql_type - type for CREATE TABLE
    cls.sql_name() - database field name
    cls.default() - default value
    cls.new(cursor) - new value of sequence __name__+'seq'
    cls.checked_init(cls,v) - check and create
    cls.valid_chars = valid chars for text field (restriction)
    self.sql_restriction() - equality restriction for sql request
    """
    __slots__ = ()
    #-------------------------------------------------------
            
    def isValid ( cls , v ) :
        try:
            cls.check(v)
        except : return 0
        return 1
    isValid = classmethod(isValid)
    #-------------------------------------------------------
            
    def sqlname ( cls ) :
        return getattr(cls,'sql_name',cls.__name__)
    sqlname = classmethod(sqlname)
    #-------------------------------------------------------
            
    def new ( cls , cursor ) :
        cursor.execute ( "select nextval('"+cls.sqlname()+"_seq')" )
        r = cursor.fetchone()
        return cls(r[0])
    new = classmethod(new)
    #-------------------------------------------------------
            
    def checked_init ( cls , v ) :
        return cls(cls.check(v))
    checked_init = classmethod(checked_init)
    #-------------------------------------------------------
            
    def sql_restriction ( self ) :
        return '('+self.__class__.sqlname() + '='+self.sql()+')'
    #-------------------------------------------------------
            
    def sql(self) : return "'"+sql_quote(str(self))+"'"
    #-------------------------------------------------------
            
    def sql_from(self,delimiter='\t') :
        return sql_quote_from(str(self),delimiter)
#-----------------------------------------------------------

class baseint (int,sql_base) :
    __slots__ = ()
    # illegal range initially - because of abstract!
    max = -1
    min = 1
    def __new__ ( cls , v=None ) :
        if v is None : v = cls.default()
        return int.__new__(cls,v)
    def sql(self) : return str(self)    # simplified
    #-------------------------------------------------------
            
    def check ( cls , v_ ) :
        des = cls.description
        if isinstance(v_,str) and len(v_)==0 : v = 0
        else :
            try: v = int(v_)
            except : raise SqlValueError(
                '<'+des+'> - must be integer.')
        if not (cls.min <= int(v) <= cls.max) :
            raise SqlValueError('<'+des+'> - must be between %d and %d.' %
                                (cls.min,cls.max) )
        return v
    check = classmethod(check)
#-----------------------------------------------------------

class basestr (str,sql_base) :
    __slots__ = ()
    valid_chars = ''
    def __new__ ( cls , v=None ) :
        if v is None : v = cls.default()
        return str.__new__(cls,v)
    def sql(self) : return "'"+sql_quote(self)+"'"
    #-------------------------------------------------------
            
    def sql_restriction(self) :
        if '*' in self :
            return '(%s LIKE %s)' % \
                   (self.__class__.sqlname(),self.sql().replace('*','%'))
        return sql_base.sql_restriction(self)
    #-------------------------------------------------------
            
    def check ( cls , s_ ) :
        des = cls.description
        try: s = str(s_)
        except : raise SqlValueError('<'+des+'> - must be string.')
        if cls.max_length and len(s)>cls.max_length :
            raise SqlValueError(
                '<'+des+'> - too long (>%d) string' %
                             cls.max_length)
        if cls.valid_chars :
            for c in s :
                if c not in cls.valid_chars :
                    raise SqlValueError(
                        '<'+des+'> - illegal character "%s". ' % c +
                        'Only one of"%s" is valid' % cls.valid_chars )
        return s
    check = classmethod(check)
#-----------------------------------------------------------

def NUMERIC(precision_,scale_) :

    class numeric (float,sql_base) :
        __slots__ = ()
        precision = precision_
        scale = scale_
        max_length = precision_+scale_+2  # sign and comma
        description = sql_type = 'NUMERIC(%d,%d)' % (precision_,scale_)

        def __new__ ( cls , v=None ) :
            if v is None : v = cls.default()
            return float.__new__(cls,v)

        def default ( cls ) : return 0.
        default=classmethod(default)
        #---------------------------------------------------
        
        def check ( cls , v_ ) :
            des = cls.description
            if isinstance ( v_ , str ) and len(v_) == 0 : v = 0.
            else :
                try:
                    v = float(v_)
                except :
                    raise SqlValueError('<'+des+
                                     '> - must be float.')

            if len('%*.*f' % (cls.precision,cls.scale,v))>cls.max_length :
                raise SqlValueError('<'+des+'> - too big (%f)'%v )
            try:
                if v < cls.min :
                    raise SqlValueError(
                        '<'+des+'> - must be greater or equal to %f' % cls.min)
            except AttributeError : pass
            try:
                if v > cls.max :
                    raise SqlValueError(
                        '<'+des+'> - must be less or equal to %f'
                        % cls.max )
            except AttributeError : pass
            return v
        check = classmethod(check)
        #---------------------------------------------------
        
        def __str__ ( self ) :
            return '%*.*f' % (self.precision,self.scale,self)
        def sql ( self ) : return str(self)
        #---------------------------------------------------

    return numeric
#-----------------------------------------------------------

def VARCHAR(length) :
    class varchar (basestr) :
        __slots__ = ()
        max_length = length
        description = sql_type = 'VARCHAR(%d)' % length

        def default ( cls ) : return ''
        default=classmethod(default)
    return varchar
#-----------------------------------------------------------

def VARIANT(sequence,delimiter=':') :
    """
    Fixed set of values with descriptors. 

    sequence - newline separated strings with 'delimiter'-separated values,
    where first element is possible value and second - description of this
    value. May be sequence of pairs (value,description). Empty strings,
    whitespaces at begin and end are ignored.
    """
    if type(sequence) == type('') :
        seq_ = sequence.split('\n')
        seq_ = map(lambda x : x.strip(),seq_)
        seq_ = filter(None,seq_)
        seq_ = map(lambda x : x.split(delimiter) , seq_)
        seq_ = [(k[0],k[1]) for k in seq_]
    else : seq_ = sequence
    
    val_ = tuple([k for k,v in seq_])
    des_ = dict(seq_)

    max_length = max([len(k) for k in val_])

    varchar = VARCHAR(max_length)
    class _variant (varchar) :
        __slots__ = ()
        values = val_
        descriptions = des_
        def default ( cls ) : return cls.values[0]
        default=classmethod(default)
    class variant (_variant) :
        __slots__ = ()
        def check (cls,v_) :
            v = super(variant,cls).check(v_)
            if v not in cls.descriptions :
                raise SqlValueError( cls.description+\
                                  ' ('+v+') must be one of: ('+\
                                  ','.join(cls.values)+')' )
            return v
        check = classmethod(check)
    return variant
#-----------------------------------------------------------

class INTEGER (baseint) :
    __slots__ = ()
    description = sql_type = 'INTEGER'
    max_length = 11                     # 10 digits and sign
    def default ( cls ) : return 0
    default=classmethod(default)
    min = -2147483648
    max = 2147483647
#-----------------------------------------------------------

class SMALLINT (baseint) :
    __slots__ = ()
    description = sql_type = 'SMALLINT'
    max_length = 6
    def default ( cls ) : return 0
    default=classmethod(default)
    min = -32768
    max = 32767
#-----------------------------------------------------------

class BOOLEAN (int,sql_base) :
    __slots__ = ()
    description = sql_type = 'BOOLEAN'
    max_length = 0

    def default ( cls ) : return 0
    default=classmethod(default)

    def check ( cls , v ) :
        try:
            if v : return 1
        except :
            raise SqlValueError('<'+cls.description+
                             '> - must be boolean.' )
        return 0
    check = classmethod(check)

    def __new__ ( cls , v=0 ) :
        if v  : v = 1
        else : v = 0
        return int.__new__(cls,v)

    def __str__ ( self ) : return '-+'[self]
    def sql ( self ) : return ( 'FALSE' , 'TRUE' )[self]
#-----------------------------------------------------------

class BIGINT (baseint) :
    __slots__ = ()
    description = sql_type = 'bigint'
    max_length = 20
    def default ( cls ) : return 0
    default=classmethod(default)
    min = -9223372036854775808
    max = 9223372036854775807
#-----------------------------------------------------------

class TEXT (basestr) :
    __slots__ = ()
    description = sql_type = 'TEXT'
    max_length = 0
    def default ( cls ) : return ''
    default=classmethod(default)
#-----------------------------------------------------------

class _INET (basestr) :
    __slots__ = ()
    description = sql_type = 'inet'
    max_length = 15
    def default ( cls ) : return '0.0.0.0'
    default=classmethod(default)
class INET (_INET) :
    def check ( cls , v ) :
        s_ = super(INET,cls).check(v)
        des = cls.description
        s = s_.split('.')
        if len(s) != 4 : raise SqlValueError(
            '<'+des+\
            '> - must be four comma separated integers.' )
        for i in s :
            if not i.isdigit() : raise SqlValueError(
                '<'+des+\
                '> - must be four comma separated integers (%s).'
                % i )
            if not (0 <= int(i) <= 255) : raise SqlValueError(
                '<'+des+\
                '> - must be between 0 and 255 (%d)'
                % int(i))
        return s_
    check = classmethod(check)
#-----------------------------------------------------------

class TIMESTAMP (object,sql_base) :
    __slots__ = 'datetime'
    description = sql_type = 'TIMESTAMP'
    max_date = mx.DateTime.DateTime(2104)
    min_date = mx.DateTime.DateTime(1904)
    format = '%d.%m.%y %T'
    format_description = mx.DateTime.now().strftime(format)
    #max_length = len(format_description)
    max_length = 30
    #default = 'NOW'??
    #-------------------------------------------------------

    def __init__ ( self , v=None ) :
        if v == None : self.datetime = mx.DateTime.now()
        elif isinstance(v,TIMESTAMP) : self.datetime = v.datetime
        elif type(v) == mx.DateTime.DateTimeType : self.datetime = v
        else : self.datetime = self._from_string(v)
    #-------------------------------------------------------

    def _from_string ( cls , s ) :
        try: return mx.DateTime.strptime(s,cls.format)
        except : return mx.DateTime.ISO.ParseDateTime(s)
    _from_string = classmethod(_from_string)
    #-------------------------------------------------------

    def copy ( self ) : return self.__class__(self.datetime)
    #-------------------------------------------------------

    def __iadd__ ( self , value ) :
        self.datetime += value
        return self
    #-------------------------------------------------------

    def __le__ ( self , other ) :
        if isinstance(other,TIMESTAMP) :
            return self.datetime <= other.datetime
        else : return self.datetime <= other
    #-------------------------------------------------------

    def __str__ ( self ) :
        return self.datetime.strftime(self.format).lstrip('0')
    def sql ( self ) : return "'"+str(self.datetime.strftime('%F %T'))+"'"
    def day ( self ) : return self.datetime.day
    #-------------------------------------------------------

    def default ( cls ) :
        return mx.DateTime.now().strftime(cls.format)
    default=classmethod(default)
    #-------------------------------------------------------

    def check ( cls , v_ ) :
        if type(v_) == mx.DateTime.DateTimeType and \
           cls.min_date <= v_ <= cls.max_date : return v_
            
        des = cls.description
        try: v = str(v_)
        except : raise SqlValueError('<'+des+'> - must be string.')
        if len(v)>cls.max_length :
            raise SqlValueError(
                '<'+des+\
                '> - too long (>%d) string' % cls.max_length)
        try:
            v = cls._from_string(v)
        except :
            raise SqlValueError(('Illegal date (%s), '
                                 'must be like ' +
                                 cls.format_description) %
                                v )
        return v
    check = classmethod(check)
#-----------------------------------------------------------

class Date (TIMESTAMP) :
    __slots__ = ()
    description = sql_type = 'Date'
    format = '%d.%m.%y'
    format_description = mx.DateTime.now().strftime(format)
    #max_length = len(format_description)
    max_length = 10
    #-------------------------------------------------------

    def sql ( self ) : return "'"+str(self.datetime.strftime('%F'))+"'"
#===========================================================
#x = VARIANT((('a','aa'),('b','bb')))()
#print x
